package com.Xht.www.dao;

import com.Xht.www.po.MerchantInfoPO;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MerchantInfoDAO extends BaseDAO {
    private TurnoverInfoDAO turnoverInfoDAO = new TurnoverInfoDAO();

    // 插入商家信息
    public boolean insertMerchantInfo(MerchantInfoPO merchantInfoPO) {
        System.out.println("merchantName: " + merchantInfoPO.getMerchantName());
        System.out.println("password: " + merchantInfoPO.getPassword());
        System.out.println("address: " + merchantInfoPO.getAddress());
        System.out.println("establishmentYear: " + merchantInfoPO.getEstablishmentYear());
        System.out.println("contactInfo: " + merchantInfoPO.getContactInfo());
        System.out.println("keywords: " + merchantInfoPO.getKeywords());
        System.out.println("businessStatus: " + merchantInfoPO.getBusinessStatus());
        System.out.println("applicationStatus: " + merchantInfoPO.getApplicationStatus());
        System.out.println("salesVolume: " + merchantInfoPO.getSalesVolume());
        System.out.println("favoritesCount: " + merchantInfoPO.getFavoritesCount());
        System.out.println("turnover: " + merchantInfoPO.getTurnover());

        if (merchantInfoPO == null ||
                merchantInfoPO.getMerchantName() == null ||
                merchantInfoPO.getPassword() == null ||
                merchantInfoPO.getAddress() == null ||
                merchantInfoPO.getEstablishmentYear() == 0 ||
                merchantInfoPO.getContactInfo() == null ||
                merchantInfoPO.getKeywords() == null ||
                merchantInfoPO.getBusinessStatus() == null ||
                merchantInfoPO.getApplicationStatus() == null) {
            System.err.println("插入商家信息时，传入的 MerchantInfoPO 对象或其属性不符合要求");
            return false;//非空检验
        }

        if (isMerchantNameExists(merchantInfoPO.getMerchantName())) {
            System.err.println("插入商家信息时，商家名称已存在");
            return false;
        }

        String sql = "INSERT INTO merchant_info (merchant_name, password, address, establishment_year, contact_info, keywords, business_status, application_status, sales_volume, favorites_count, turnover) VALUES (?,?,?,?,?,?,?,?,?,?,?)";

        try (Connection connection = getConnection()) {
            connection.setAutoCommit(false); // 开启事务

            try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
                pstmt.setString(1, merchantInfoPO.getMerchantName());
                pstmt.setString(2, merchantInfoPO.getPassword());
                pstmt.setString(3, merchantInfoPO.getAddress());
                pstmt.setInt(4, merchantInfoPO.getEstablishmentYear());
                pstmt.setString(5, merchantInfoPO.getContactInfo());
                pstmt.setString(6, merchantInfoPO.getKeywords());
                pstmt.setString(7, merchantInfoPO.getBusinessStatus());
                pstmt.setString(8, merchantInfoPO.getApplicationStatus());
                pstmt.setInt(9, merchantInfoPO.getSalesVolume());
                pstmt.setInt(10, merchantInfoPO.getFavoritesCount());
                pstmt.setBigDecimal(11, BigDecimal.ZERO); // 初始化为0，后续会更新
                pstmt.executeUpdate();
            }

            // 同步营业额
            updateMerchantTurnover(connection, merchantInfoPO.getMerchantName());

            connection.commit(); // 提交事务
            return true;
        } catch (SQLException e) {
            try (Connection conn = getConnection()) {
                if (conn != null) conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
            return false;
        }
    }

    // 检查商家名称是否已存在
    private boolean isMerchantNameExists(String merchantName) {
        String sql = "SELECT COUNT(*) FROM merchant_info WHERE merchant_name = ?";
        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, merchantName);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 查询所有商家，并支持按年份，销量排序
    public List<MerchantInfoPO> getAllMerchantInfoSorted(String sortField, String sortOrder) {
        List<MerchantInfoPO> merchantInfoList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT merchant_id, merchant_name, password, address, establishment_year, contact_info, keywords, business_status, application_status, sales_volume, favorites_count, turnover FROM merchant_info WHERE application_status = '已通过'");

        // 添加排序逻辑
        if (sortField != null && !sortField.isEmpty()) {
            String order = "ASC";
            if ("desc".equalsIgnoreCase(sortOrder)) {
                order = "DESC";
            }

            switch (sortField) {
                case "establishmentYear":
                    sql.append(" ORDER BY establishment_year ").append(order);
                    break;
                case "salesVolume":
                    sql.append(" ORDER BY sales_volume ").append(order);
                    break;
                default:
                    sql.append(" ORDER BY establishment_year ASC");
            }
        } else {
            sql.append(" ORDER BY establishment_year ASC"); // 默认排序
        }

        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql.toString());
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                MerchantInfoPO merchantInfoPO = new MerchantInfoPO();
                merchantInfoPO.setMerchantId(rs.getInt("merchant_id"));
                merchantInfoPO.setMerchantName(rs.getString("merchant_name"));
                merchantInfoPO.setPassword(rs.getString("password"));
                merchantInfoPO.setAddress(rs.getString("address"));
                merchantInfoPO.setEstablishmentYear(rs.getInt("establishment_year"));
                merchantInfoPO.setContactInfo(rs.getString("contact_info"));
                merchantInfoPO.setKeywords(rs.getString("keywords"));
                merchantInfoPO.setBusinessStatus(rs.getString("business_status"));
                merchantInfoPO.setApplicationStatus(rs.getString("application_status"));
                Integer salesVolume = rs.getInt("sales_volume");
                if (!rs.wasNull()) {
                    merchantInfoPO.setSalesVolume(salesVolume);
                }
                Integer favoritesCount = rs.getInt("favorites_count");
                if (!rs.wasNull()) {
                    merchantInfoPO.setFavoritesCount(favoritesCount);
                }
                merchantInfoPO.setTurnover(rs.getBigDecimal("turnover"));
                merchantInfoList.add(merchantInfoPO);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return merchantInfoList;
    }

    // 更新商家信息
    public void updateMerchantInfo(MerchantInfoPO merchantInfoPO) {
        if (merchantInfoPO == null || merchantInfoPO.getMerchantId() == null) {
            System.err.println("更新商家信息时，传入的 MerchantInfoPO 对象或其属性不符合要求");
            return;
        }

        // 检查是否需要更新商家名称
        boolean needUpdateMerchantName = merchantInfoPO.getMerchantName() != null;
        String oldMerchantName = null;
        if (needUpdateMerchantName) {
            MerchantInfoPO oldMerchantInfo = getMerchantById(merchantInfoPO.getMerchantId());
            if (oldMerchantInfo != null) {
                oldMerchantName = oldMerchantInfo.getMerchantName();
            }
        }

        StringBuilder sql = new StringBuilder("UPDATE merchant_info SET ");
        List<Object> params = new ArrayList<>();

        //非空检查
        boolean hasSetValue = false;

        if (merchantInfoPO.getMerchantName() != null) {
            if (hasSetValue) sql.append(", ");
            sql.append("merchant_name = ?");
            params.add(merchantInfoPO.getMerchantName());
            hasSetValue = true;
        }

        if (merchantInfoPO.getPassword() != null) {
            if (hasSetValue) sql.append(", ");
            sql.append("password = ?");
            params.add(merchantInfoPO.getPassword());
            hasSetValue = true;
        }

        if (merchantInfoPO.getAddress() != null) {
            if (hasSetValue) sql.append(", ");
            sql.append("address = ?");
            params.add(merchantInfoPO.getAddress());
            hasSetValue = true;
        }

        if (merchantInfoPO.getEstablishmentYear() != 0) {
            if (hasSetValue) sql.append(", ");
            sql.append("establishment_year = ?");
            params.add(merchantInfoPO.getEstablishmentYear());
            hasSetValue = true;
        }

        if (merchantInfoPO.getContactInfo() != null) {
            if (hasSetValue) sql.append(", ");
            sql.append("contact_info = ?");
            params.add(merchantInfoPO.getContactInfo());
            hasSetValue = true;
        }

        if (merchantInfoPO.getKeywords() != null) {
            if (hasSetValue) sql.append(", ");
            sql.append("keywords = ?");
            params.add(merchantInfoPO.getKeywords());
            hasSetValue = true;
        }

        if (merchantInfoPO.getBusinessStatus() != null) {
            if (hasSetValue) sql.append(", ");
            sql.append("business_status = ?");
            params.add(merchantInfoPO.getBusinessStatus());
            hasSetValue = true;
        }

        if (merchantInfoPO.getApplicationStatus() != null) {
            if (hasSetValue) sql.append(", ");
            sql.append("application_status = ?");
            params.add(merchantInfoPO.getApplicationStatus());
            hasSetValue = true;
        }

        if (merchantInfoPO.getSalesVolume() != 0) {
            if (hasSetValue) sql.append(", ");
            sql.append("sales_volume = ?");
            params.add(merchantInfoPO.getSalesVolume());
            hasSetValue = true;
        }

        if (merchantInfoPO.getFavoritesCount() != 0) {
            if (hasSetValue) sql.append(", ");
            sql.append("favorites_count = ?");
            params.add(merchantInfoPO.getFavoritesCount());
            hasSetValue = true;
        }

        if (!hasSetValue) {
            System.err.println("更新商家信息时，没有需要更新的字段");
            return;
        }

        sql.append(" WHERE merchant_id = ?");
        params.add(merchantInfoPO.getMerchantId());

        try (Connection connection = getConnection()) {
            connection.setAutoCommit(false); // 开启事务

            // 更新room_type_info表中的merchant_name
            if (needUpdateMerchantName && oldMerchantName != null) {
                String updateRoomTypeSql = "UPDATE room_type_info SET merchant_name = ? WHERE merchant_name = ?";
                try (PreparedStatement pstmt = connection.prepareStatement(updateRoomTypeSql)) {
                    pstmt.setString(1, merchantInfoPO.getMerchantName());
                    pstmt.setString(2, oldMerchantName);
                    pstmt.executeUpdate();
                }
            }

            // 更新merchant_info表
            try (PreparedStatement pstmt = connection.prepareStatement(sql.toString())) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
                pstmt.executeUpdate();
            }

            // 同步营业额
            String merchantName = merchantInfoPO.getMerchantName() != null
                    ? merchantInfoPO.getMerchantName()
                    : getMerchantById(merchantInfoPO.getMerchantId()).getMerchantName();
            updateMerchantTurnover(connection, merchantName);

            connection.commit(); // 提交事务
        } catch (SQLException e) {
            try (Connection conn = getConnection()) {
                if (conn != null) conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
    }

    //查找已通过审核的商家
    public List<MerchantInfoPO> searchApprovedMerchants(String keyword, String sortField, String sortOrder, int pageNumber, int pageSize) {
        List<MerchantInfoPO> merchantInfoList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM merchant_info WHERE application_status = '已通过'");

        if (keyword != null && !keyword.isEmpty()) {
            sql.append(" AND (merchant_name LIKE ? OR keywords LIKE ?)");
        }

        appendSortCondition(sql, sortField, sortOrder);
        sql.append(" LIMIT ? OFFSET ?");

        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql.toString())) {

            int paramIndex = 1;

            if (keyword != null && !keyword.isEmpty()) {
                pstmt.setString(paramIndex++, "%" + keyword + "%");
                pstmt.setString(paramIndex++, "%" + keyword + "%");
            }

            pstmt.setInt(paramIndex++, pageSize);
            pstmt.setInt(paramIndex++, (pageNumber - 1) * pageSize);

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                MerchantInfoPO merchant = new MerchantInfoPO();
                merchant.setMerchantId(rs.getInt("merchant_id"));
                merchant.setMerchantName(rs.getString("merchant_name"));
                merchant.setPassword(rs.getString("password"));
                merchant.setAddress(rs.getString("address"));
                merchant.setEstablishmentYear(rs.getInt("establishment_year"));
                merchant.setContactInfo(rs.getString("contact_info"));
                merchant.setKeywords(rs.getString("keywords"));
                merchant.setBusinessStatus(rs.getString("business_status"));
                merchant.setApplicationStatus(rs.getString("application_status"));
                Integer salesVolume = rs.getInt("sales_volume");
                if (!rs.wasNull()) {
                    merchant.setSalesVolume(salesVolume);
                }
                Integer favoritesCount = rs.getInt("favorites_count");
                if (!rs.wasNull()) {
                    merchant.setFavoritesCount(favoritesCount);
                }
                merchant.setTurnover(rs.getBigDecimal("turnover"));

                merchantInfoList.add(merchant);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return merchantInfoList;
    }

    //通过名称搜索已通过审核的商家
    public List<MerchantInfoPO> searchApprovedMerchantsByName(String keyword, String sortField, String sortOrder, int pageNumber, int pageSize) {
        List<MerchantInfoPO> merchantInfoList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM merchant_info WHERE application_status = '已通过' AND merchant_name LIKE ?");

        appendSortCondition(sql, sortField, sortOrder);
        sql.append(" LIMIT ? OFFSET ?");

        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql.toString())) {

            int paramIndex = 1;
            pstmt.setString(paramIndex++, "%" + keyword + "%");
            pstmt.setInt(paramIndex++, pageSize);
            pstmt.setInt(paramIndex++, (pageNumber - 1) * pageSize);

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                MerchantInfoPO merchant = new MerchantInfoPO();
                merchant.setMerchantId(rs.getInt("merchant_id"));
                merchant.setMerchantName(rs.getString("merchant_name"));
                merchant.setPassword(rs.getString("password"));
                merchant.setAddress(rs.getString("address"));
                merchant.setEstablishmentYear(rs.getInt("establishment_year"));
                merchant.setContactInfo(rs.getString("contact_info"));
                merchant.setKeywords(rs.getString("keywords"));
                merchant.setBusinessStatus(rs.getString("business_status"));
                merchant.setApplicationStatus(rs.getString("application_status"));
                Integer salesVolume = rs.getInt("sales_volume");
                if (!rs.wasNull()) {
                    merchant.setSalesVolume(salesVolume);
                }
                Integer favoritesCount = rs.getInt("favorites_count");
                if (!rs.wasNull()) {
                    merchant.setFavoritesCount(favoritesCount);
                }
                merchant.setTurnover(rs.getBigDecimal("turnover"));

                merchantInfoList.add(merchant);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return merchantInfoList;
    }

    //通过关键字搜索已通过审核的商家
    public List<MerchantInfoPO> searchApprovedMerchantsByKeyword(String keyword, String sortField, String sortOrder, int pageNumber, int pageSize) {
        List<MerchantInfoPO> merchantInfoList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM merchant_info WHERE application_status = '已通过' AND keywords LIKE ?");

        appendSortCondition(sql, sortField, sortOrder);
        sql.append(" LIMIT ? OFFSET ?");

        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql.toString())) {

            int paramIndex = 1;
            pstmt.setString(paramIndex++, "%" + keyword + "%");
            pstmt.setInt(paramIndex++, pageSize);
            pstmt.setInt(paramIndex++, (pageNumber - 1) * pageSize);

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                MerchantInfoPO merchant = new MerchantInfoPO();
                merchant.setMerchantId(rs.getInt("merchant_id"));
                merchant.setMerchantName(rs.getString("merchant_name"));
                merchant.setPassword(rs.getString("password"));
                merchant.setAddress(rs.getString("address"));
                merchant.setEstablishmentYear(rs.getInt("establishment_year"));
                merchant.setContactInfo(rs.getString("contact_info"));
                merchant.setKeywords(rs.getString("keywords"));
                merchant.setBusinessStatus(rs.getString("business_status"));
                merchant.setApplicationStatus(rs.getString("application_status"));
                Integer salesVolume = rs.getInt("sales_volume");
                if (!rs.wasNull()) {
                    merchant.setSalesVolume(salesVolume);
                }
                Integer favoritesCount = rs.getInt("favorites_count");
                if (!rs.wasNull()) {
                    merchant.setFavoritesCount(favoritesCount);
                }
                merchant.setTurnover(rs.getBigDecimal("turnover"));

                merchantInfoList.add(merchant);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return merchantInfoList;
    }

    //商家排序条件
    private void appendSortCondition(StringBuilder sql, String sortField, String sortOrder) {
        if (sortField == null || sortField.isEmpty()) {
            sql.append(" ORDER BY establishment_year ASC");
            return;
        }

        String order = "ASC";
        if ("desc".equalsIgnoreCase(sortOrder)) {
            order = "DESC";
        }

        switch (sortField) {
            case "establishmentYear":
                sql.append(" ORDER BY establishment_year ").append(order);
                break;
            case "salesVolume":
                sql.append(" ORDER BY sales_volume ").append(order);
                break;
            default:
                sql.append(" ORDER BY establishment_year ASC");
        }
    }

    // 统计符合条件的商家总数
    public int countApprovedMerchants(String keyword) {
        String sql = "SELECT COUNT(*) FROM merchant_info WHERE application_status = '已通过'";
        if (keyword != null && !keyword.isEmpty()) {
            sql += " AND (merchant_name LIKE ? OR keywords LIKE ?)";
        }

        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {

            int paramIndex = 1;
            if (keyword != null && !keyword.isEmpty()) {
                pstmt.setString(paramIndex++, "%" + keyword + "%");
                pstmt.setString(paramIndex++, "%" + keyword + "%");
            }

            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }


    // 按名称搜索时统计总数
    public int countApprovedMerchantsByName(String keyword) {
        String sql = "SELECT COUNT(*) FROM merchant_info WHERE application_status = '已通过' AND merchant_name LIKE ?";
        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, "%" + keyword + "%");
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 按关键字搜索时统计总数
    public int countApprovedMerchantsByKeyword(String keyword) {
        String sql = "SELECT COUNT(*) FROM merchant_info WHERE application_status = '已通过' AND keywords LIKE ?";
        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, "%" + keyword + "%");
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 实现根据名称查询商家的方法
    public MerchantInfoPO getMerchantByName(String name) {
        String sql = "SELECT * FROM merchant_info WHERE merchant_name = ?";
        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, name);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    MerchantInfoPO merchant = new MerchantInfoPO();
                    merchant.setMerchantId(rs.getInt("merchant_id"));
                    merchant.setMerchantName(rs.getString("merchant_name"));
                    merchant.setPassword(rs.getString("password"));
                    merchant.setApplicationStatus(rs.getString("application_status")); // 确保字段名正确
                    // 其他字段...
                    return merchant;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //实现根据id查找商家的方法
    public MerchantInfoPO getMerchantById(int merchantId) {
        MerchantInfoPO merchant = null;
        String sql = "SELECT * FROM merchant_info WHERE merchant_id = ?";
        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, merchantId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    merchant = new MerchantInfoPO();
                    merchant.setMerchantId(rs.getInt("merchant_id"));
                    merchant.setMerchantName(rs.getString("merchant_name"));
                    merchant.setPassword(rs.getString("password"));
                    merchant.setAddress(rs.getString("address"));
                    merchant.setEstablishmentYear(rs.getInt("establishment_year"));
                    merchant.setContactInfo(rs.getString("contact_info"));
                    merchant.setKeywords(rs.getString("keywords"));
                    Integer salesVolume = rs.getInt("sales_volume");
                    if (!rs.wasNull()) {
                        merchant.setSalesVolume(salesVolume);
                    }
                    Integer favoritesCount = rs.getInt("favorites_count");
                    if (!rs.wasNull()) {
                        merchant.setFavoritesCount(favoritesCount);
                    }
                    merchant.setTurnover(rs.getBigDecimal("turnover"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return merchant;
    }

    //根据名称查找商家id的方法
    public Integer getMerchantIdByName(String name) {
        String sql = "SELECT merchant_id FROM merchant_info WHERE merchant_name = ?";
        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, name);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("merchant_id");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

   //  获取所有商家信息的方法，不进行筛选
    public List<MerchantInfoPO> getAllMerchantsWithoutFilter() {
        List<MerchantInfoPO> merchantList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection(); // 使用BaseDAO提供的方法获取连接
            String sql = "SELECT * FROM merchant_info";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();

            while (rs.next()) {
                MerchantInfoPO merchant = new MerchantInfoPO();
                merchant.setMerchantId(rs.getInt("merchant_id"));
                merchant.setMerchantName(rs.getString("merchant_name"));
                merchant.setAddress(rs.getString("address"));
                merchant.setEstablishmentYear(rs.getInt("establishment_year"));
                merchant.setContactInfo(rs.getString("contact_info"));
                merchant.setKeywords(rs.getString("keywords"));
                merchant.setBusinessStatus(rs.getString("business_status"));
                merchant.setApplicationStatus(rs.getString("application_status"));

                // 处理数值类型，解决类型不匹配问题
                Integer salesVolume = null;
                if (!rs.wasNull()) {
                    salesVolume = rs.getInt("sales_volume");
                }
                merchant.setSalesVolume(salesVolume);

                java.math.BigDecimal turnover = null;
                if (!rs.wasNull()) {
                    turnover = rs.getBigDecimal("turnover");
                }
                merchant.setTurnover(turnover);

                // 处理可为空的整数字段
                Integer favoritesCount = rs.getInt("favorites_count");
                merchant.setFavoritesCount(rs.wasNull() ? null : favoritesCount);

                merchant.setPassword(rs.getString("password"));

                merchantList.add(merchant);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 使用BaseDAO提供的方法释放资源
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                releaseConnection(conn); // 调用BaseDAO的方法释放连接
            }
        }

        return merchantList;
    }

    // 获取所有状态为"申请中"的商家
    public List<MerchantInfoPO> getPendingApplications() {
        List<MerchantInfoPO> applicationList = new ArrayList<>();
        String sql = "SELECT * FROM merchant_info WHERE application_status = '申请中'";

        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                MerchantInfoPO merchant = new MerchantInfoPO();
                merchant.setMerchantId(rs.getInt("merchant_id"));
                merchant.setMerchantName(rs.getString("merchant_name"));
                merchant.setAddress(rs.getString("address"));
                merchant.setEstablishmentYear(rs.getInt("establishment_year"));
                merchant.setContactInfo(rs.getString("contact_info"));
                merchant.setKeywords(rs.getString("keywords"));
                merchant.setBusinessStatus(rs.getString("business_status"));
                merchant.setApplicationStatus(rs.getString("application_status"));

                // 处理可为空的字段
                Integer salesVolume = rs.getInt("sales_volume");
                merchant.setSalesVolume(rs.wasNull() ? null : salesVolume);

                Integer favoritesCount = rs.getInt("favorites_count");
                merchant.setFavoritesCount(rs.wasNull() ? null : favoritesCount);

                merchant.setTurnover(rs.getBigDecimal("turnover"));

                applicationList.add(merchant);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return applicationList;
    }

    // 处理店铺入驻申请，修改商家状态
    public boolean processApplication(int merchantId, String action) {
        if (merchantId <= 0 || (!"approve".equalsIgnoreCase(action) && !"reject".equalsIgnoreCase(action))) {
            System.err.println("处理申请时，商家ID或操作类型无效");
            return false;
        }

        String status = action.equalsIgnoreCase("approve") ? "已通过" : "未通过";
        String sql = "UPDATE merchant_info SET application_status = ? WHERE merchant_id = ?";

        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, status);
            pstmt.setInt(2, merchantId);
            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 处理商家营业状态（正常/黑商)
    public boolean updateBusinessStatus(int merchantId, String status) {
        if (merchantId <= 0 || (!"黑商".equals(status) && !"正常".equals(status))) {
            System.err.println("更新商家状态时，商家ID或状态无效");
            return false;
        }

        String sql = "UPDATE merchant_info SET business_status = ? WHERE merchant_id = ?";

        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, status);
            pstmt.setInt(2, merchantId);
            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 同步商家信息表中的营业额
    public int syncSalesVolumeFromSalesTable() {
        String sql = "UPDATE merchant_info mi " +
                "JOIN ( " +
                "    SELECT merchant_name, SUM(quantity_sold) AS total_sales " +
                "    FROM sales_volume_info " +
                "    GROUP BY merchant_name " +
                ") sv ON mi.merchant_name = sv.merchant_name " +
                "SET mi.sales_volume = sv.total_sales";

        try (Connection connection = getConnection();
             Statement stmt = connection.createStatement()) {
            return stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    // 同步商家营业额的辅助方法
    // 在MerchantInfoDAO类的updateMerchantTurnover方法中添加调试输出
    private void updateMerchantTurnover(Connection connection, String merchantName) throws SQLException {
        // 添加调试输出
        System.out.println("开始同步商家 " + merchantName + " 的营业额");

        // 从turnover_info表获取最新营业额
        BigDecimal turnover = turnoverInfoDAO.getTurnoverByMerchant(connection, merchantName);

        // 添加调试输出
        System.out.println("从营业额表查询到的营业额: " + turnover);

        // 更新merchant_info表的turnover字段
        String sql = "UPDATE merchant_info SET turnover = ? WHERE merchant_name = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setBigDecimal(1, turnover);
            pstmt.setString(2, merchantName);
            int rows = pstmt.executeUpdate();

            // 添加调试输出
            System.out.println("更新了 " + rows + " 条记录的营业额");
        }
    }

    // 更新商家收藏量
    public boolean updateFavoritesCount(int merchantId, int change) {
        String sql = "UPDATE merchant_info SET favorites_count = favorites_count + ? WHERE merchant_id = ?";
        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, change);
            pstmt.setInt(2, merchantId);
            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
}

